Synopsis: Fear of the Unknown

In our example bugs database, the Accounts table has columns first_name and last_name. We can use an expression to format the user’s full name as a single column using the string concatenation operator:

Let’s suppose that your boss asks you to modify the database to add the user’s middle initial to the table (perhaps two users have the same first and last name, and the middle initial is an excellent way to avoid confusion). This seems to be a pretty simple alteration and you manually add the middle initials for a few users.

Suddenly, the application ceases to show any names. Then, on a second look, you notice that this isn’t universal. Only the names of the users who have specified their middle initials appear normally; everyone else’s name is now blank.

What happened to everyone else’s names? Can you fix this before your boss notices and starts to panic, thinking you’ve lost the data in the database?

Objective: Distinguish missing values#

Some data in our database inevitably have no value. We need to insert a row before discovering the values for all of the columns, and some columns legitimately don’t have any meaningful values at all. SQL supports a unique NULL value corresponding to the NULL keyword.

There are many ways we can use a null value productively in SQL tables and queries:

• We can use NULL in place of a value that is not available when the row is created, such as the date of termination for an employee who is still working.

• A given column can use NULL when it has no applicable value in a given row, such as the fuel efficiency rating for a fully electric car.

• A function can return NULL when given invalid inputs, as in DAY('2009-12-32').

• An outer join uses NULL values as placeholders for the columns of an unmatched table in an outer join.

Our objective is to write queries against columns that contain NULL.

Legitimate uses of the antipattern#

Using NULL is not the antipattern; the antipattern is using NULL like an ordinary value or using an ordinary value like NULL.

We need to treat NULL as an ordinary value when you import or export the external data. In a text file with comma-separated fields, all values must be represented by text. For example, in MySQL’s mysqlimport tool for loading data from a text file, \N represents a NULL.

Similarly, user input cannot represent a NULL directly. An application that accepts user input may provide a way to map some special input sequence to NULL. For example, Microsoft .NET 2.0 and its newer versions support a property called ConvertEmptyStringToNull for web user interfaces. Parameters and bound fields with this property automatically convert an empty string value (“”) to NULL.

Finally, NULL won’t work if we need to support several distinct missing-value cases. Let’s say we want to distinguish between a bug that has never been assigned and a bug that was previously assigned to a person who has left the project — we have to use a distinct value for each state.